package cn.edu.jxau.dao;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.jxau.domain.PageBean;
import cn.edu.jxau.domain.post;

import cn.edu.jxau.domain.reply;
import cn.edu.jxau.domain.user;
import cn.edu.jxau.domain.userInfo;
import cn.itcast.jdbc.TxQueryRunner;

public class PostDao {
	private QueryRunner qr = new TxQueryRunner();

	/**
	 * 添加发的帖子的信息进数据库
	 * 
	 * @param post
	 */

	public void addPost(post post){

		String sql="insert into post values(?,?,?,?,?,?,?,?)";
		Object[] params={post.getPostId(),post.getTitle(),post.getContent(),post.getPoster(),post.getTime(),post.getType(),post.getPageView(),post.getValuable()};
		try {
			qr.update(sql, params);
		} catch (SQLException e) {
			throw new RuntimeException();
		}
	}

	/**
	 * 根据帖子类型查出所有帖子 返回值：post对象的集合
	 */
	public List<post> findByType(String type) {
		String sql = "select * from post where type=? ";
		try {
			return qr.query(sql, new BeanListHandler<post>(post.class), type);
		} catch (SQLException e) {
			throw new RuntimeException();
		}
	}

	/**
	 * 查询所有帖子
	 */
	public HashMap<user, userInfo> queryUserInfo() {
		HashMap<user, userInfo> map = new HashMap<user, userInfo>();
		String sql = "select *from user";
		try {
			List<user> users = qr.query(sql, new BeanListHandler<user>(user.class));
			sql = "select *from userInfo where username=?";
			for (user user : users) {
				userInfo userInfo = qr.query(sql, new BeanHandler<userInfo>(userInfo.class), user.getUsername());
				map.put(user, userInfo);
			}
			return map;
		} catch (SQLException e) {
			throw new RuntimeException();
		}
	}

	/**
	 * 这是一个按发帖时间排序的帖子
	 * 
	 * @param pc
	 * @param ps
	 * @param value
	 * @return
	 * @throws SQLException
	 */

	public PageBean<post> findAllByType(int pc, int ps, String type) throws SQLException {
		// TODO Auto-generated method stub
		/*
		 * 1. 总记录数 2. 当前页记录数
		 */

		int tr = total(type);// 得到该类型的总记录数

		String sql = "select * from post where type=?  order by time desc limit ?,?";
		Object[] params = { type, (pc - 1) * ps, ps };
		List<post> postList = qr.query(sql, new BeanListHandler<post>(post.class), params);

		PageBean<post> pb = new PageBean<post>();
		pb.setPc(pc);
		pb.setPs(ps);
		pb.setTr(tr);
		pb.setDatas(postList);

		return pb;

	}

	/**
	 * 这是一个按浏览量排序的帖子
	 * 
	 * @param pc
	 * @param ps
	 * @param value
	 * @return
	 * @throws SQLException
	 */

	public PageBean<post> findAllByType1(int pc, int ps, String type) throws SQLException {
		// TODO Auto-generated method stub
		/*
		 * 1. 总记录数 2. 当前页记录数
		 */

		int tr = total(type);// 得到该类型的总记录数

		String sql = "select * from post where type=?  order by pageView desc limit ?,?";
		Object[] params = { type, (pc - 1) * ps, ps };
		List<post> postList = qr.query(sql, new BeanListHandler<post>(post.class), params);

		PageBean<post> pb = new PageBean<post>();
		pb.setPc(pc);
		pb.setPs(ps);
		pb.setTr(tr);
		pb.setDatas(postList);

		return pb;

	}

	/**
	 * 这是一个查询出帖子前五的帖子 若帖子数少于五那就全部查询出来
	 * 
	 * @param pc
	 * @param ps
	 * @param value
	 * @return
	 * @throws SQLException
	 */

	public PageBean<post> findAllByType2(int pc, int ps, String type) throws SQLException {
		// TODO Auto-generated method stub
		/*
		 * 1. 总记录数 2. 当前页记录数
		 */

		int tr = total(type);// 得到该类型的总记录数

		if (tr > 5) {
			tr = 5;
		}
		String sql = "select * from post where type=?  order by pageView desc limit ?,?";
		Object[] params = { type, 0, tr };
		List<post> postList = qr.query(sql, new BeanListHandler<post>(post.class), params);

		PageBean<post> pb = new PageBean<post>();
		pb.setPc(pc);
		pb.setPs(ps);
		pb.setTr(tr);
		pb.setDatas(postList);

		return pb;

	}

	/**
	 * 查出所有的精贴
	 * 
	 * @param pc
	 * @param ps
	 * @param value
	 * @return
	 * @throws SQLException
	 */
	public PageBean<post> findAllByType3(int pc, int ps, String type) throws SQLException {
		// TODO Auto-generated method stub
		/*
		 * 1. 总记录数 2. 当前页记录数
		 */

		int tr = total(type);// 得到该类型的总记录数

		if (tr > 5) {
			tr = 5;
		}
		String sql = "select * from post where type=? and valuable=? order by time desc limit ?,?";
		Object[] params = { type, "1", 0, tr };

		List<post> postList = qr.query(sql, new BeanListHandler<post>(post.class), params);
		// /*
		// * 判断postlist是否为空，若为空则将postlist中的对象的type赋值为type，这是为了让jsp获取到该值
		// * 让其他超链接得到该值
		// */
		// if(postList==null){
		// postList=new ArrayList<post>();
		//
		// postList.add(new post());
		//
		// }
		PageBean<post> pb = new PageBean<post>();
		pb.setPc(pc);
		pb.setPs(ps);
		pb.setTr(tr);
		pb.setDatas(postList);

		return pb;

	}

	/**
	 * 这是一个根据类型统计帖子总数的
	 * 
	 * @param type
	 * @return
	 */
	public int total(String type) {
		String sql = "select count(*) from post where type=?";
		try {
			return ((Number) qr.query(sql, new ScalarHandler(), type)).intValue();
		} catch (SQLException e) {
			throw new RuntimeException();
		}
	}

	/**
	 * 统计帖子总数
	 * 
	 * @return
	 */

	public int postTotal() {
		String sql = "select count(*) from post";
		try {
			return ((Number) qr.query(sql, new ScalarHandler())).intValue();
		} catch (SQLException e) {
			throw new RuntimeException();
		}
	}

	/**
	 * 统计在线人数
	 * 
	 * @return
	 */

	public int onlineTotal() {
		String sql = "select count(*) from userinfo where state=?";
		try {
			return ((Number) qr.query(sql, new ScalarHandler(), "1")).intValue();
		} catch (SQLException e) {
			throw new RuntimeException();
		}
	}

	/**
	 * 查询出最新的五个帖子 若少于五个帖子就把所有帖查出来
	 */
	public List<post> findNewTie() {
		String sql = "select * from post order by time limit ?,?";
		int t = postTotal();
		if (t > 4) {
			t = 4;
		}
		Object[] params = { 0, t };
		try {
			return qr.query(sql, new BeanListHandler<post>(post.class), params);
		} catch (SQLException e) {
			throw new RuntimeException();
		}
	}

	/**
	 * 查询出浏览量前4的帖子 若少于五个帖子就把所有帖查出来
	 */
	public List<post> findPageView() {
		String sql = "select * from post order by pageView limit ?,?";
		int t = postTotal();
		if (t > 4) {
			t = 4;
		}
		Object[] params = { 0, t };
		try {
			return qr.query(sql, new BeanListHandler<post>(post.class), params);
		} catch (SQLException e) {
			throw new RuntimeException();
		}
	}

	/**
	 * 根据帖子id查询出帖子的全部信息
	 * 
	 * @param postId
	 * @return
	 */

	public Map<post, userInfo> findById(String postId) {
		Map map = new HashMap<post, user>();
		String sql = "select * from post where postId=?";
		try {

			post post = qr.query(sql, new BeanHandler<post>(post.class), postId);

			sql = "select * from userinfo where username=?";

			userInfo userInfo = qr.query(sql, new BeanHandler<userInfo>(userInfo.class), post.getPoster());

			map.put(post, userInfo);

			return map;

		} catch (SQLException e) {
			throw new RuntimeException();
		}

	}

	/**
	 * 根据发帖的id找出所有该帖相关的回复和回帖人的userInfo
	 * 
	 * @param postId
	 * @return
	 */

	public Map<userInfo, reply> findAllReplyBy(String postId) {
		String sql = "select * from reply where reply_postid=?";
		Map<userInfo, reply> map = new HashMap<userInfo, reply>();
		try {

			List<reply> replyList = qr.query(sql, new BeanListHandler<reply>(reply.class), postId);
			sql = "select * from userinfo where username=?";
			for (reply reply : replyList) {
				userInfo userInfo = qr.query(sql, new BeanHandler<userInfo>(userInfo.class), reply.getReply_user());
				map.put(userInfo, reply);
			}
			return map;
		} catch (SQLException e) {
			throw new RuntimeException();
		}

	}

	/**
	 * 根据发帖id找到该帖的全部回复数量
	 * 
	 * @param postId
	 * @return
	 */
	public int Replytotal(String postId) {
		String sql = "select count(*) from reply where reply_postid=?";
		try {
			return ((Number) qr.query(sql, new ScalarHandler(), postId)).intValue();
		} catch (SQLException e) {
			throw new RuntimeException();
		}
	}

	public void updatePageView(String postId) {
		String sql = "update post set pageView=pageView+1 where postId=?";
		try {
			qr.update(sql, postId);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
